﻿Imports System.Threading

Public Class Acad_Apply_Form




    Private Sub Acad_Apply_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        GridTools.Enabled = False

        LoadTypeBox()
        LoadPeriodBox(PeriodBox)
        LoadTerritoryBox()
        LoadLABox()


        PeriodBox.SelectedValue = CurrentPeriodID

    End Sub

    Private Sub LoadTypeBox()
        LoadComboBox(TypeBox, "select * from dbo.Acad_Funding_Methods", "Funding_ID", "Funding_Name")
    End Sub

    Private Sub LoadTerritoryBox()
        Dim sql As String

        sql = "select Territory_Code, Territory_Name from dbo.Territories order by Territory_name"

        LoadComboBox(TerritoryBox, sql, "Territory_Code", "Territory_Name")


    End Sub

    Private Sub LoadLABox()
        Dim sql As String

        Try
            sql = "select '<All>' as LA_Code, '<All>' as LA_Name union " & _
                "select la.LA_Code, la.LA_Name from Local_Authorities la, Regions r, TerritoryJoin t " & _
                "where r.Region_Code = t.Region_Code and r.Region_Code = la.Region_Code and t.Territory_Code = '" & TerritoryBox.SelectedValue & "' order by LA_Name"

            LoadComboBox(LABox, sql, "LA_Code", "LA_Name")
        Catch ex As Exception

        End Try
        
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If TypeBox.SelectedValue = "CON" Then
            LoadCONData()
        Else
            LoadNonCONData()
        End If

        GridTools.Enabled = True


    End Sub

    Private Sub LoadCONData()
        Dim Period_ID
        Dim sql As String

        Dim TheRids As DataView
        Dim rec As DataRowView

        Dim LACodeSelectString As String = ""

        Period_ID = PeriodBox.SelectedValue


        If LABox.SelectedValue = "<All>" Then
            LACodeSelectString = ""
        Else
            LACodeSelectString = "and LA_Code = '" & LABox.SelectedValue & "'"
        End If

        sql = "Select RID from all_provider_fields where lead = 1 and Provider_Type_Code = 'Acade' " & LACodeSelectString & " and Territory_Code = '" & TerritoryBox.SelectedValue & "' and UAI in (select UAI from Acad_Additional_Provider_Info where Funding_ID = '" & TypeBox.SelectedValue & "')"


        TheRids = LoadDataView(sql)


        With ProgressBarForm
            .Text = "Calculating Allocations..."
            .Show()
            .TheProgressBar.Value = 0
            .TheProgressBar.Minimum = 0
            .TheProgressBar.Maximum = TheRids.Count
            .TheProgressBar.Step = 1
            .Refresh()
        End With

        Try
            RunNonQuery("delete from Acad_Summary_" & CurrentUser.Name)
        Catch ex As Exception

        End Try


        For Each rec In TheRids
            sql = "select " & _
                "a.RID, " & _
                "a.Provider_Name as [Provider Name], " & _
                "a.UAI, " & _
                "a.UPIN, " & _
                "a.Region_Name as [Region], " & _
                "a.LA_Name as [Local Authority], " & _
                " " & _
                "cast(coalesce(a.A03,0) - coalesce(h.A03,0) as int) as [No of Pupils N-Y13], " & _
                "cast(coalesce(a.A04,0) - coalesce(h.A04,0) as int) as [No of Pupiles SA/SA+], " & _
                "cast(coalesce(a.A05,0) - coalesce(h.A05,0) as decimal(36,2)) as [Total LA SBS Inc any 6th for funding from LA], " & _
                "cast(coalesce(a.A06,0) - coalesce(h.A06,0) as decimal(36,2)) as [Total AWPU Funding], " & _
                "cast(coalesce(a.A07,0) - coalesce(h.A07,0) as decimal(36,2)) as [LA Sixth Form Funding], " & _
                "cast(coalesce(a.A38,0) - coalesce(h.A38,0) as decimal(36,2)) as [Total Early Years AWPU], " & _
                "cast(coalesce(a.A40,0) - coalesce(h.A40,0) as decimal(36,2)) as [Total Special Place-led funding], " & _
                "cast(coalesce(a.A08,0) - coalesce(h.A08,0) as decimal(36,2)) as [Total Additional Pupil Led Funding], " & _
                "cast(coalesce(a.A09,0) - coalesce(h.A09,0) as decimal(36,2)) as [Total Other Pupil or Place Led Funding], " & _
                "cast(coalesce(a.A10,0) - coalesce(h.A10,0) as decimal(36,2)) as [Total AEN learning needs associated with EAL], " & _
                "cast(coalesce(a.A11,0) - coalesce(h.A11,0) as decimal(36,2)) as [Total AEN identified Special Educational Needs (pupil led)], " & _
                "cast(coalesce(a.A12,0) - coalesce(h.A12,0) as decimal(36,2)) as [Total AEN identified Special Educational Needs (non-pupil led)], " & _
                "cast(coalesce(a.A13,0) - coalesce(h.A13,0) as decimal(36,2)) as [Total AEN-Other Learning needs], " & _
                "cast(coalesce(a.A14,0) - coalesce(h.A14,0) as decimal(36,2)) as [Total AEN-Social needs], " & _
                "cast(coalesce(a.A15,0) - coalesce(h.A15,0) as decimal(36,2)) as [Total Site Specific factors (including pupil led)], " & _
                "cast(coalesce(a.A16,0) - coalesce(h.A16,0) as decimal(36,2)) as [Total Early Years Specific Factors], " & _
                "cast(coalesce(a.A17,0) - coalesce(h.A17,0) as decimal(36,2)) as [Total School Specific factors (including pupil led], " & _
                "cast(coalesce(a.A18,0) - coalesce(h.A18,0) as decimal(36,2)) as [Historical Grants Factors (may include MSG)], " & _
                "cast(coalesce(a.A19,0) - coalesce(h.A19,0) as decimal(36,2)) as [Total budget adjuestments], " & _
                "cast(coalesce(a.A20,0) - coalesce(h.A20,0) as decimal(36,2)) as [Minimum Funding Adjustments], " & _
                "cast(coalesce(a.A21,0) - coalesce(h.A21,0) as decimal(36,2)) as [Rates], " & _
                "cast(coalesce(a.A22,0) - coalesce(h.A22,0) as decimal(36,2)) as [Insurance], " & _
                "cast(coalesce(a.A23,0) - coalesce(h.A23,0) as decimal(36,2)) as [Individuall Assigned Resources (LA Funded)], " & _
                "cast(coalesce(a.A24,0) - coalesce(h.A24,0) as decimal(36,2)) as [LA 6th Form Funding], " & _
                "cast(coalesce(a.A25,0) - coalesce(h.A25,0) as decimal(36,2)) as [YPLA 6th Form Allocation], " & _
                "cast(coalesce(a.A26,0) - coalesce(h.A26,0) as decimal(36,2)) as [YPLA SBS Allocation to 31 August 2012 (inc any Rates adj)], " & _
                "cast(coalesce(a.A27,0) - coalesce(h.A27,0) as decimal(36,2)) as [LACSEG General], " & _
                "cast(coalesce(a.A28,0) - coalesce(h.A28,0) as decimal(36,2)) as [LACSEG SEN], " & _
                "cast(coalesce(a.A29,0) - coalesce(h.A29,0) as decimal(36,2)) as [Total LACSEG], " & _
                "cast(coalesce(a.A30,0) - coalesce(h.A30,0) as decimal(36,2)) as [General], " & _
                "cast(coalesce(a.A31,0) - coalesce(h.A31,0) as decimal(36,2)) as [SEN], " & _
                "cast(coalesce(a.A32,0) - coalesce(h.A32,0) as decimal(36,2)) as [Threshold & Performance Pay], " & _
                "cast(coalesce(a.A33,0) - coalesce(h.A33,0) as decimal(36,2)) as [Rates 30% (Mandatory Relief Rate)], " & _
                "cast(coalesce(a.A34,0) - coalesce(h.A34,0) as decimal(36,2)) as [Total GAG Funding Payable], " & _
                " " & _
                "a.a03, a.a04, a.a05, a.a06, a.a07, a.a08, a.a09, a.a10, a.a11, a.a12, a.a13, a.a14, a.a15, a.a16, a.a17," & _
                "a.a18, a.a19, a.a20, a.a21, a.a22, a.a23, a.a24, a.a25, a.a26, a.a27, a.a28, a.a29, a.a30, a.a31, a.a32," & _
                "a.a33, a.a34, a.a38, a.a40," & _
                " coalesce(h.The_Date,0) as The_Date, " & _
                " 0 as Apply" & _
                " " & _
                "from  " & _
                "VW_Acad_Convert_Annex_A a " & _
                " " & _
                "left outer join " & _
                " " & _
                "(select h2.* from dbo.Acad_Applied_History_Con h2, " & _
                "(select RID, max(The_Date) as The_Date from Acad_Applied_History_Con group by rid) m " & _
                "where m.RID = h2.RID and m.The_Date = h2.The_Date) h  " & _
                " " & _
                "on (h.RID = a.RID and h.Period_ID = a.Period_ID) " & _
                " " & _
                "where  " & _
                "a.period_id = " & Period_ID & _
                " and a.rid = '" & rec.Item("RID") & "' "

            'if this is the first record then drop and create the table

            If ProgressBarForm.TheProgressBar.Value = 0 Then
                RunNonQuery("drop table Acad_Summary_" & CurrentUser.Name)

                sql = "select * into Acad_Summary_" & CurrentUser.Name & " from (" & sql & ") a"
                RunNonQuery(sql)
            Else
                sql = "insert into Acad_Summary_" & CurrentUser.Name & " " & sql
                RunNonQuery(sql)
            End If


            ProgressBarForm.TheProgressBar.PerformStep()

        Next

        If TheRids.Count = 0 Then
            'create blank table
            RunNonQuery("drop table Acad_Summary_" & CurrentUser.Name)

            sql = "select * into Acad_Summary_" & CurrentUser.Name & " from (select null as [No records found]) a"
            RunNonQuery(sql)
        End If

        ProgressBarForm.TheProgressBar.Value = 0
        ProgressBarForm.Close()

        LoadGrid()

    End Sub


    Private Sub LoadNonCONData()
        Dim Period_ID
        Dim sql As String

        Dim TheRids As DataView
        Dim rec As DataRowView

        Dim LACodeSelectString As String = ""

        Period_ID = PeriodBox.SelectedValue


        If LABox.SelectedValue = "<All>" Then
            LACodeSelectString = ""
        Else
            LACodeSelectString = "and LA_Code = '" & LABox.SelectedValue & "'"
        End If

        sql = "Select RID from all_provider_fields where lead = 1 and Provider_Type_Code = 'Acade' " & LACodeSelectString & " and Territory_Code = '" & TerritoryBox.SelectedValue & "' and UAI in (select UAI from Acad_Additional_Provider_Info where Funding_ID = '" & TypeBox.SelectedValue & "')"


        TheRids = LoadDataView(sql)


        With ProgressBarForm
            .Text = "Calculating Allocations..."
            .Show()
            .TheProgressBar.Value = 0
            .TheProgressBar.Minimum = 0
            .TheProgressBar.Maximum = TheRids.Count
            .TheProgressBar.Step = 1
            .Refresh()
        End With

        Try
            RunNonQuery("delete from Acad_Summary_" & CurrentUser.Name)
        Catch ex As Exception

        End Try


        For Each rec In TheRids
            sql = "select " & _
                "a.RID, " & _
                "a.Provider_Name as [Provider Name], " & _
                "a.UAI, " & _
                "a.UPIN, " & _
                "a.Region_Name as [Region], " & _
                "a.LA_Name as [Local Authority], " & _
                " " & _
                "cast(coalesce(a.A1,0) - coalesce(h.A1,0) as int) as [USLA YG'S 1 TO 6], " & _
                "cast(coalesce(a.A2,0) - coalesce(h.A2,0) as int) as [USLA YG'S 7 TO 11], " & _
                "cast(coalesce(a.A3,0) - coalesce(h.A3,0) as int) as [OSLA YG'S 12 TO 14], " & _
                "cast(coalesce(a.A5,0) - coalesce(h.A5,0) as decimal(36,2)) as [A School Budget Share], " & _
                "cast(coalesce(a.A6,0) - coalesce(h.A6,0) as decimal(36,2)) as [B LA Central Spend Equivalent Grant], " & _
                "cast(coalesce(a.A7,0) - coalesce(h.A7,0) as decimal(36,2)) as [C Insurance], " & _
                "cast(coalesce(a.A8,0) - coalesce(h.A8,0) as decimal(36,2)) as [D Teacher Threshold Devolved], " & _
                "cast(coalesce(a.A9,0) - coalesce(h.A9,0) as decimal(36,2)) as [E Mainstreamed Grants], " & _
                "cast(coalesce(a.A10,0) - coalesce(h.A10,0) as decimal(36,2)) as [F Start Up Grant a], " & _
                "cast(coalesce(a.A11,0) - coalesce(h.A11,0) as decimal(36,2)) as [Gi Start Up Grant b Formulaic], " & _
                "cast(coalesce(a.A12,0) - coalesce(h.A12,0) as decimal(36,2)) as [Gii Start Up Grant b Assessment], " & _
                "cast(coalesce(a.EAG_Amount,0) - coalesce(h.EAG_Amount,0) as decimal(36,2)) as [H EAG], " & _
                "cast(coalesce(a.A15,0) - coalesce(h.A15,0) as decimal(36,2)) as [L GAG Advances and Abatements], " & _
                "cast(coalesce(a.Sch_All_Post16_Allocation,0) - coalesce(h.Sch_All_Post16_Allocation,0) as decimal(36,2)) as [M Post 16 Funding], " & _
                " " & _
                "cast((coalesce(a.A5,0) + " & _
                "coalesce(a.A6,0) + " & _
                "coalesce(a.A7,0) + " & _
                "coalesce(a.A8,0) + " & _
                "coalesce(a.A9,0) + " & _
                "coalesce(a.A10,0) + " & _
                "coalesce(a.A11,0) + " & _
                "coalesce(a.A12,0) + " & _
                "coalesce(a.A15,0) + " & _
                "coalesce(a.EAG_Amount,0)) - " & _
                " " & _
                "(coalesce(h.A5,0) + " & _
                "coalesce(h.A6,0) + " & _
                "coalesce(h.A7,0) + " & _
                "coalesce(h.A8,0) + " & _
                "coalesce(h.A9,0) + " & _
                "coalesce(h.A10,0) + " & _
                "coalesce(h.A11,0) + " & _
                "coalesce(h.A12,0) + " & _
                "coalesce(h.A15,0) + " & _
                "coalesce(h.EAG_Amount,0)) as decimal(36,2)) as [TOTAL Difference], " & _
                " " & _
                "cast(coalesce(a.A1,0) + " & _
                "coalesce(a.A2,0) + " & _
                "coalesce(a.A3,0) as int) as [Total FTEs], " & _
                " " & _
                "cast(coalesce(a.A5,0) + " & _
                "coalesce(a.A6,0) + " & _
                "coalesce(a.A7,0) + " & _
                "coalesce(a.A8,0) + " & _
                "coalesce(a.A9,0) + " & _
                "coalesce(a.A10,0) + " & _
                "coalesce(a.A11,0) + " & _
                "coalesce(a.A12,0) + " & _
                "coalesce(a.A15,0) + " & _
                "coalesce(a.EAG_Amount,0) as decimal(36,2)) as TOTAL, " & _
                "0 as Apply," & _
                " " & _
                "coalesce(a.A1,0) as A1, " & _
                "coalesce(a.A2,0) as A2, " & _
                "coalesce(a.A3,0) as A3, " & _
                "coalesce(a.A5,0) as A5, " & _
                "coalesce(a.A6,0) as A6, " & _
                "coalesce(a.A7,0) as A7, " & _
                "coalesce(a.A8,0) as A8, " & _
                "coalesce(a.A9,0) as A9, " & _
                "coalesce(a.A10,0) as A10, " & _
                "coalesce(a.A11,0) as A11, " & _
                "coalesce(a.A12,0) as A12, " & _
                "coalesce(a.A15,0) as A15, " & _
                "coalesce(a.EAG_Amount,0) as EAG_Amount, " & _
                "coalesce(a.Sch_All_Post16_Allocation,0) as Sch_All_Post16_Allocation, coalesce(h.The_Date,0) as The_Date " & _
                " " & _
                "from  " & _
                "VW_Acad_Annex_A_Main a " & _
                " " & _
                "left outer join " & _
                " " & _
                "(select h2.* from dbo.Acad_Applied_History h2, " & _
                "(select RID, max(The_Date) as The_Date from Acad_Applied_History group by rid) m " & _
                "where m.RID = h2.RID and m.The_Date = h2.The_Date) h  " & _
                " " & _
                "on (h.RID = a.RID and h.Period_ID = a.Period_ID) " & _
                " " & _
                "where  " & _
                "a.period_id = " & Period_ID & _
                " and a.rid = '" & rec.Item("RID") & "' "

            'if this is the first record then drop and create the table

            If ProgressBarForm.TheProgressBar.Value = 0 Then
                RunNonQuery("drop table Acad_Summary_" & CurrentUser.Name)

                sql = "select * into Acad_Summary_" & CurrentUser.Name & " from (" & sql & ") a"
                RunNonQuery(sql)
            Else
                sql = "insert into Acad_Summary_" & CurrentUser.Name & " " & sql
                RunNonQuery(sql)
            End If


            ProgressBarForm.TheProgressBar.PerformStep()

        Next

        If TheRids.Count = 0 Then
            'create blank table
            RunNonQuery("drop table Acad_Summary_" & CurrentUser.Name)

            sql = "select * into Acad_Summary_" & CurrentUser.Name & " from (select null as [No records found]) a"
            RunNonQuery(sql)
        End If

        ProgressBarForm.TheProgressBar.Value = 0
        ProgressBarForm.Close()

        LoadGrid()

    End Sub

    Private Sub LoadGrid()
        Dim sql As String

        sql = "select * from Acad_Summary_" & CurrentUser.Name

        DataGrid.DataSource = LoadDataView(sql)

        If DataGrid.RowCount = 0 Then Exit Sub

        If DataGrid.Columns.Count = 1 Then Exit Sub

        'set the style
        With DataGrid
            .Columns("RID").Frozen = True
            .Columns("RID").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            .Columns("RID").Visible = False

            .Columns("Provider Name").Frozen = True
            .Columns("Provider Name").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader

            .Columns("UAI").Frozen = True
            .Columns("UAI").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader

            .Columns("UPIN").Frozen = True
            .Columns("UPIN").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader

        End With

        Dim c As Integer

        For c = 0 To DataGrid.Columns.Count - 1

            Select Case DataGrid.Columns(c).ValueType.ToString
                Case "System.String"
                    'keep as is
                Case "System.Int32"
                    DataGrid.Columns(c).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                Case "System.Decimal"
                    DataGrid.Columns(c).DefaultCellStyle.Format = "c"
                    DataGrid.Columns(c).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                Case Else

            End Select
            DataGrid.Columns(c).ReadOnly = True

            'hide the actaul values
           
            Select Case DataGrid.Columns(c).Name

                Case "A1", "A2", "A3", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "EAG_Amount", "Sch_All_Post16_Allocation", "A15"
                    DataGrid.Columns(c).Visible = False
                Case "The_Date"
                    DataGrid.Columns(c).Visible = False
                Case "a03", "a04", "a05", "a06", "a07", "a08", "a09", "a10", "a11", "a12", "a13", "a14", "a15", "a16", "a17", "a18", "a19", "a20", "a21", "a22", "a23", "a24", "a25", "a26", "a27", "a28", "a29", "a30", "a31", "a32", "a33", "a34", "a38", "a40"
                    DataGrid.Columns(c).Visible = False

            End Select
        Next

        'set the check box column
        With DataGrid

            Dim index As Integer

            Dim TickColumn As New DataGridViewCheckBoxColumn

            TickColumn.Name = "Apply"
            TickColumn.DataPropertyName = "Apply"

            TickColumn.ThreeState = False
            TickColumn.TrueValue = 1
            TickColumn.FalseValue = 0

            index = .Columns.IndexOf(.Columns("Apply"))
            .Columns.RemoveAt(index)
            .Columns.Insert(index, TickColumn)
            .Columns("Apply").ReadOnly = False
        End With




    End Sub


    Private Sub TerritoryBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoryBox.SelectedIndexChanged
        LoadLABox()

    End Sub


    Private Sub DataGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGrid.CellFormatting
        If e.RowIndex = -1 Then Exit Sub
        If e.ColumnIndex = -1 Then Exit Sub

        If DataGrid.Columns(e.ColumnIndex).ValueType.ToString = "System.Int32" Or DataGrid.Columns(e.ColumnIndex).ValueType.ToString = "System.Decimal" Then
            If Not IsDBNull(DataGrid.Item(e.ColumnIndex, e.RowIndex).Value) Then
                If DataGrid.Item(e.ColumnIndex, e.RowIndex).Value = 0 Then
                    e.CellStyle.ForeColor = Color.LightGray

                End If
            End If
        End If


    End Sub

    Private Sub LABox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LABox.SelectedIndexChanged
        DataGrid.DataSource = Nothing

        GridTools.Enabled = False

    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged
        DataGrid.DataSource = Nothing
        GridTools.Enabled = False
    End Sub

    Private Sub DataGrid_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGrid.ColumnHeaderMouseClick
        If DataGrid.Columns(e.ColumnIndex).Name = "Apply" Then
            TickUntickAll()
        End If
    End Sub

    Private Sub TickUntickAll()
        If DataGrid.RowCount = 0 Then Exit Sub

        Dim i As Integer
        Dim tick As Integer

        If DataGrid.Item("Apply", 0).Value = 0 Then
            tick = 1
        Else
            tick = 0
        End If

        For i = 0 To DataGrid.RowCount - 1
            With DataGrid
                If tick = 1 Then
                    ' If DataGrid.Item("TOTAL Difference", i).Value <> 0 Then
                    .Item("Apply", i).Value = tick
                    'End If
                Else
                .Item("Apply", i).Value = tick
                End If
            End With
        Next
    End Sub

    Private Sub LoadHistory(ByVal RID As String, ByVal FromDate As DateTime)
        Dim sql As String
        Dim HistoryTable As String

        If TypeBox.SelectedValue = "CON" Then
            HistoryTable = "Acad_Applied_History_Con"
        Else
            HistoryTable = "Acad_Applied_History"
        End If

        sql = "select * from " & _
                "(select distinct cast(day(TheDate) as nvarchar(2)) + ' ' + Datename(Month,TheDate) + ' ' + cast(year(TheDate) as nvarchar(4)) as [Date],  " & _
                "[user_Name] as [User], DATEADD(dd, 0, DATEDIFF(dd, 0, TheDate)) as Actual_Date, RID, Period_ID from dbo.Acad_GAG_Data) sub " & _
                "where Actual_Date >= " & FromDate.ToOADate - 2 & " " & _
                "and RID = '" & RID & "' " & _
                "and Period_ID = " & PeriodBox.SelectedValue & " " & _
                "order by Actual_Date desc"

        DatasetGrid.DataSource = LoadDataView(sql)

        sql = "select * from " & _
                "(select distinct cast(day(The_Date) as nvarchar(2)) + ' ' + Datename(Month,The_Date) + ' ' + cast(year(The_Date) as nvarchar(4)) as [Date],  " & _
                "[user_Name] as [User], DATEADD(dd, 0, DATEDIFF(dd, 0, The_Date)) as Actual_Date, RID, Period_ID from " & HistoryTable & ") sub " & _
                "where RID = '" & RID & "' " & _
                "and Period_ID = " & PeriodBox.SelectedValue & " " & _
                "order by Actual_Date desc "

        AppliedHistoryGrid.DataSource = LoadDataView(sql)


        With DatasetGrid
            .Columns("RID").Visible = False
            .Columns("Actual_Date").Visible = False
            .Columns("Period_ID").Visible = False
        End With

        With AppliedHistoryGrid
            .Columns("RID").Visible = False
            .Columns("Actual_Date").Visible = False
            .Columns("Period_ID").Visible = False
        End With

    End Sub

    Private Sub DataGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellEnter

        With DataGrid
            If .Columns.Count > 1 Then
                DataSetLabel.Text = "Dataset Changes - " & .Item("Provider Name", e.RowIndex).Value
                AppliedHistoryLabel.Text = "Applied History - " & .Item("Provider Name", e.RowIndex).Value
                LoadHistory(.Item("RID", e.RowIndex).Value, .Item("The_Date", e.RowIndex).Value)
            End If

        End With
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        If DataGrid.RowCount = 0 Then Exit Sub

        If DataGrid.Columns.Count = 1 Then Exit Sub

        DataGrid.CurrentCell = DataGrid.Item("Provider Name", 0)

        If TypeBox.SelectedValue = "CON" Then
            ApplyAllocationsCON()
        Else
            ApplyAllocationsNonCON()
        End If


    End Sub

    Private Sub ApplyAllocationsCON()

        'first check if anything has been selected to approve
        Dim r As Integer
        Dim Items As Integer = 0
        Dim ApplyDate As DateTime
        Dim sql As String

        For r = 0 To DataGrid.RowCount - 1
            If DataGrid.Item("Apply", r).Value = 1 Then
                Items = Items + 1
            End If
        Next

        If Items = 0 Then
            MessageBox.Show("No records have been selected, please check the records you wish to apply", "No records selected", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If MessageBox.Show("You have selected " & Items.ToString & " records to apply, are you sure you wish to continue?", "Apply selected records?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If
        ShowLoadingForm()

        Me.Refresh()

        ApplyDate = Now

        Dim A03 As Integer = 0
        Dim A04 As Integer = 0
        Dim A05 As Decimal = 0
        Dim A06 As Decimal = 0
        Dim A07 As Decimal = 0
        Dim A08 As Decimal = 0
        Dim A09 As Decimal = 0
        Dim A10 As Decimal = 0
        Dim A11 As Decimal = 0
        Dim A12 As Decimal = 0
        Dim A13 As Decimal = 0
        Dim A14 As Decimal = 0
        Dim A15 As Decimal = 0
        Dim A16 As Decimal = 0
        Dim A17 As Decimal = 0
        Dim A18 As Decimal = 0
        Dim A19 As Decimal = 0
        Dim A20 As Decimal = 0
        Dim A21 As Decimal = 0
        Dim A22 As Decimal = 0
        Dim A23 As Decimal = 0
        Dim A24 As Decimal = 0
        Dim A25 As Decimal = 0
        Dim A26 As Decimal = 0
        Dim A27 As Decimal = 0
        Dim A28 As Decimal = 0
        Dim A29 As Decimal = 0
        Dim A30 As Decimal = 0
        Dim A31 As Decimal = 0
        Dim A32 As Decimal = 0
        Dim A33 As Decimal = 0
        Dim A34 As Decimal = 0
        
        Dim RID As String = ""

        For r = 0 To DataGrid.RowCount - 1

            If DataGrid.Item("Apply", r).Value = 1 Then

                With DataGrid
                  
                    A03 = .Item("a03", r).Value
                    A04 = .Item("a04", r).Value
                    A05 = .Item("a05", r).Value
                    A06 = .Item("a06", r).Value
                    A07 = .Item("a07", r).Value
                    A08 = .Item("a08", r).Value
                    A09 = .Item("a09", r).Value
                    A10 = .Item("a10", r).Value
                    A11 = .Item("a11", r).Value
                    A12 = .Item("a12", r).Value
                    A13 = .Item("a13", r).Value
                    A14 = .Item("a14", r).Value
                    A15 = .Item("a15", r).Value
                    A16 = .Item("a16", r).Value
                    A17 = .Item("a17", r).Value
                    A18 = .Item("a18", r).Value
                    A19 = .Item("a19", r).Value
                    A20 = .Item("a20", r).Value
                    A21 = .Item("a21", r).Value
                    A22 = .Item("a22", r).Value
                    A23 = .Item("a23", r).Value
                    A24 = .Item("a24", r).Value
                    A25 = .Item("a25", r).Value
                    A26 = .Item("a26", r).Value
                    A27 = .Item("a27", r).Value
                    A28 = .Item("a28", r).Value
                    A29 = .Item("a29", r).Value
                    A30 = .Item("a30", r).Value
                    A31 = .Item("a31", r).Value
                    A32 = .Item("a32", r).Value
                    A33 = .Item("a33", r).Value
                    A34 = .Item("a34", r).Value

                    RID = .Item("RID", r).Value
                End With

                sql = " DECLARE @A3 AS INT SET @A3 = " & A03 & _
                      " DECLARE @A4 AS INT SET @A4 = " & A04 & _
                      " DECLARE @A5 AS MONEY SET @A5 = " & A05 & _
                      " DECLARE @A6 AS MONEY SET @A6 = " & A06 & _
                      " DECLARE @A7 AS MONEY SET @A7 = " & A07 & _
                      " DECLARE @A8 AS MONEY SET @A8 = " & A08 & _
                      " DECLARE @A9 AS MONEY SET @A9 = " & A09 & _
                      " DECLARE @A10 AS MONEY SET @A10 = " & A10 & _
                      " DECLARE @A11 AS MONEY SET @A11 = " & A11 & _
                      " DECLARE @A12 AS MONEY SET @A12 = " & A12 & _
                      " DECLARE @A13 AS MONEY SET @A13 = " & A13 & _
                      " DECLARE @A14 AS MONEY SET @A14 = " & A14 & _
                      " DECLARE @A15 AS MONEY SET @A15 = " & A15 & _
                      " DECLARE @A16 AS MONEY SET @A16 = " & A16 & _
                      " DECLARE @A17 AS MONEY SET @A17 = " & A17 & _
                      " DECLARE @A18 AS MONEY SET @A18 = " & A18 & _
                      " DECLARE @A19 AS MONEY SET @A19 = " & A19 & _
                      " DECLARE @A20 AS MONEY SET @A20 = " & A20 & _
                      " DECLARE @A21 AS MONEY SET @A21 = " & A21 & _
                      " DECLARE @A22 AS MONEY SET @A22 = " & A22 & _
                      " DECLARE @A23 AS MONEY SET @A23 = " & A23 & _
                      " DECLARE @A24 AS MONEY SET @A24 = " & A24 & _
                      " DECLARE @A25 AS MONEY SET @A25 = " & A25 & _
                      " DECLARE @A26 AS MONEY SET @A26 = " & A26 & _
                      " DECLARE @A27 AS MONEY SET @A27 = " & A27 & _
                      " DECLARE @A28 AS MONEY SET @A28 = " & A28 & _
                      " DECLARE @A29 AS MONEY SET @A29 = " & A29 & _
                      " DECLARE @A30 AS MONEY SET @A30 = " & A30 & _
                      " DECLARE @A31 AS MONEY SET @A31 = " & A31 & _
                      " DECLARE @A32 AS MONEY SET @A32 = " & A32 & _
                      " DECLARE @A33 AS MONEY SET @A33 = " & A33 & _
                      " DECLARE @A34 AS MONEY SET @A34 = " & A34 & _
                      " DECLARE @The_Date as DateTime SET @The_Date = " & ApplyDate.ToOADate - 2 & _
                      " DECLARE @User_Name as nvarchar(50) SET @User_Name = '" & CurrentUser.Name & "'" & _
                      " DECLARE @Period_ID as INT SET @Period_ID = " & PeriodBox.SelectedValue.ToString & _
                      " DECLARE @RID as nvarchar(11) SET @RID = '" & RID & "'" & _
                      " " & _
                      "INSERT INTO [Acad_Applied_History_Con] " & _
                        "           ([The_Date] " & _
                        "           ,[User_Name] " & _
                        "           ,[Period_ID] " & _
                        "           ,[RID] " & _
                        "           ,[A03] " & _
                        "           ,[A04] " & _
                        "           ,[A05] " & _
                        "           ,[A06] " & _
                        "           ,[A07] " & _
                        "           ,[A08] " & _
                        "           ,[A09] " & _
                        "           ,[A10] " & _
                        "           ,[A11] " & _
                        "           ,[A12] " & _
                        "           ,[A13] " & _
                        "           ,[A14] " & _
                        "           ,[A15] " & _
                        "           ,[A16] " & _
                        "           ,[A17] " & _
                        "           ,[A18] " & _
                        "           ,[A19] " & _
                        "           ,[A20] " & _
                        "           ,[A21] " & _
                        "           ,[A22] " & _
                        "           ,[A23] " & _
                        "           ,[A24] " & _
                        "           ,[A25] " & _
                        "           ,[A26] " & _
                        "           ,[A27] " & _
                        "           ,[A28] " & _
                        "           ,[A29] " & _
                        "           ,[A30] " & _
                        "           ,[A31] " & _
                        "           ,[A32] " & _
                        "           ,[A33] " & _
                        "           ,[A34]) " & _
                        "     VALUES " & _
                        "           (@The_Date " & _
                        "           ,@User_Name " & _
                        "           ,@Period_ID " & _
                        "           ,@RID " & _
                        "           ,@A3 " & _
                        "           ,@A4 " & _
                        "           ,@A5 " & _
                        "           ,@A6 " & _
                        "           ,@A7 " & _
                        "           ,@A8 " & _
                        "           ,@A9 " & _
                        "           ,@A10 " & _
                        "           ,@A11 " & _
                        "           ,@A12 " & _
                        "           ,@A13 " & _
                        "           ,@A14 " & _
                        "           ,@A15 " & _
                        "           ,@A16 " & _
                        "           ,@A17 " & _
                        "           ,@A18 " & _
                        "           ,@A19 " & _
                        "           ,@A20 " & _
                        "           ,@A21 " & _
                        "           ,@A22 " & _
                        "           ,@A23 " & _
                        "           ,@A24 " & _
                        "           ,@A25 " & _
                        "           ,@A26 " & _
                        "           ,@A27 " & _
                        "           ,@A28 " & _
                        "           ,@A29 " & _
                        "           ,@A30 " & _
                        "           ,@A31 " & _
                        "           ,@A32 " & _
                        "           ,@A33 " & _
                        "           ,@A34) "

                RunNonQuery(sql)



                'set the current allocation to history if applicable
                sql = "update Allocations set Status = 'History' where RID = '" & RID & "' and Allocation_ID like 'YPE%' and Period_ID = " & PeriodBox.SelectedValue
                RunNonQuery(sql)


                'now add it to the allocations table

                Dim y As Integer
                Dim Allocation_ID As String = ""
                Dim Allocation As Decimal = 0

                For y = 26 To 33

                    Select Case y
                        Case 26
                            Allocation_ID = "YPE01"
                            Allocation = A26 - A25
                        Case 29
                            Allocation_ID = "YPE02"
                            Allocation = A29
                        Case 32
                            Allocation_ID = "YPE04"
                            Allocation = A32
                        Case 33
                            Allocation_ID = "YPE11"
                            Allocation = A33
                        Case Else
                            Allocation_ID = ""
                    End Select

                    If Allocation_ID <> "" Then
                        sql = " DECLARE @Period_ID as INT SET @Period_ID = " & PeriodBox.SelectedValue.ToString & _
                        " DECLARE @RID as nvarchar(11) SET @RID = '" & RID & "'" & _
                        " DECLARE @Allocation_ID as nchar(5) SET @Allocation_ID = '" & Allocation_ID & "'" & _
                        " DECLARE @The_Date as DateTime SET @The_Date = " & ApplyDate.ToOADate - 2 & _
                        " DECLARE @User_Name as nvarchar(50) SET @User_Name = '" & CurrentUser.Name & "'" & _
                        " DECLARE @Allocation AS MONEY SET @Allocation = " & Allocation & _
                        " DECLARE @Learner_Numbers AS INT SET @Learner_Numbers = " & A03 + A04 & _
                        " INSERT INTO [Allocations] " & _
                        "           ([Period_ID] " & _
                        "           ,[RID] " & _
                        "           ,[Allocation_ID] " & _
                        "           ,[TheDate] " & _
                        "           ,[User_Name] " & _
                        "           ,[Allocation] " & _
                        "           ,[Rate] " & _
                        "           ,[Provider_Factor] " & _
                        "           ,[Learner_Numbers] " & _
                        "           ,[Status] " & _
                        "           ,[Notes]) " & _
                        "     VALUES " & _
                        "           (@Period_ID " & _
                        "           ,@RID " & _
                        "           ,@Allocation_ID " & _
                        "           ,@The_Date " & _
                        "           ,@User_Name " & _
                        "           ,@Allocation " & _
                        "           ,0 " & _
                        "           ,0 " & _
                        "           ,@Learner_Numbers " & _
                        "           ,'Approved' " & _
                        "           ,'') "

                        RunNonQuery(sql)
                    End If
                    UpdateActivePayments(PeriodBox.SelectedValue, RID, Allocation_ID)
                Next

            End If
        Next
        HideLoadingForm()

        MessageBox.Show("The Allocations have been applied", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        LoadCONData()

    End Sub

    Private Sub ApplyAllocationsNonCON()

        'first check if anything has been selected to approve
        Dim r As Integer
        Dim Items As Integer = 0
        Dim ApplyDate As DateTime
        Dim sql As String

        For r = 0 To DataGrid.RowCount - 1
            If DataGrid.Item("Apply", r).Value = 1 Then
                Items = Items + 1
            End If
        Next

        If Items = 0 Then
            MessageBox.Show("No records have been selected, please check the records you wish to apply", "No records selected", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If MessageBox.Show("You have selected " & Items.ToString & " records to apply, are you sure you wish to continue?", "Apply selected records?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If
        ShowLoadingForm()

        Me.Refresh()

        ApplyDate = Now

        Dim A1 As Integer = 0
        Dim A2 As Integer = 0
        Dim A3 As Integer = 0
        Dim A5 As Decimal = 0
        Dim A6 As Decimal = 0
        Dim A7 As Decimal = 0
        Dim A8 As Decimal = 0
        Dim A9 As Decimal = 0
        Dim A10 As Decimal = 0
        Dim A11 As Decimal = 0
        Dim A12 As Decimal = 0
        Dim A15 As Decimal = 0
        Dim EAG_Amount As Decimal = 0
        Dim Sch_All_Post16_Allocation As Decimal = 0
        Dim RID As String = ""

        For r = 0 To DataGrid.RowCount - 1

            If DataGrid.Item("Apply", r).Value = 1 Then

                With DataGrid
                    A1 = .Item("A1", r).Value
                    A2 = .Item("A2", r).Value
                    A3 = .Item("A3", r).Value
                    A5 = .Item("A5", r).Value
                    A6 = .Item("A6", r).Value
                    A7 = .Item("A7", r).Value
                    A8 = .Item("A8", r).Value
                    A9 = .Item("A9", r).Value
                    A10 = .Item("A10", r).Value
                    A11 = .Item("A11", r).Value
                    A12 = .Item("A12", r).Value
                    A15 = .Item("A15", r).Value
                    EAG_Amount = .Item("EAG_Amount", r).Value
                    Sch_All_Post16_Allocation = .Item("Sch_All_Post16_Allocation", r).Value
                    RID = .Item("RID", r).Value
                End With

                sql = " DECLARE @A1 AS INT SET @A1 = " & A1 & _
                      " DECLARE @A2 AS INT SET @A2 = " & A2 & _
                      " DECLARE @A3 AS INT SET @A3 = " & A3 & _
                      " DECLARE @A5 AS MONEY SET @A5 = " & A5 & _
                      " DECLARE @A6 AS MONEY SET @A6 = " & A6 & _
                      " DECLARE @A7 AS MONEY SET @A7 = " & A7 & _
                      " DECLARE @A8 AS MONEY SET @A8 = " & A8 & _
                      " DECLARE @A9 AS MONEY SET @A9 = " & A9 & _
                      " DECLARE @A10 AS MONEY SET @A10 = " & A10 & _
                      " DECLARE @A11 AS MONEY SET @A11 = " & A11 & _
                      " DECLARE @A12 AS MONEY SET @A12 = " & A12 & _
                      " DECLARE @A15 AS MONEY SET @A15 = " & A15 & _
                      " DECLARE @EAG_AMOUNT AS MONEY SET @EAG_AMOUNT = " & EAG_Amount & _
                      " DECLARE @Sch_All_Post16_Allocation AS MONEY SET @Sch_All_Post16_Allocation = " & Sch_All_Post16_Allocation & _
                      " DECLARE @The_Date as DateTime SET @The_Date = " & ApplyDate.ToOADate - 2 & _
                      " DECLARE @User_Name as nvarchar(50) SET @User_Name = '" & CurrentUser.Name & "'" & _
                      " DECLARE @Period_ID as INT SET @Period_ID = " & PeriodBox.SelectedValue.ToString & _
                      " DECLARE @RID as nvarchar(11) SET @RID = '" & RID & "'" & _
                      " " & _
                      "INSERT INTO [Acad_Applied_History] " & _
                        "           ([The_Date] " & _
                        "           ,[User_Name] " & _
                        "           ,[Period_ID] " & _
                        "           ,[RID] " & _
                        "           ,[A1] " & _
                        "           ,[A2] " & _
                        "           ,[A3] " & _
                        "           ,[A5] " & _
                        "           ,[A6] " & _
                        "           ,[A7] " & _
                        "           ,[A8] " & _
                        "           ,[A9] " & _
                        "           ,[A10] " & _
                        "           ,[A11] " & _
                        "           ,[A12] " & _
                        "           ,[EAG_Amount] " & _
                        "           ,[Sch_All_Post16_Allocation] " & _
                        "           ,[A15]) " & _
                        "     VALUES " & _
                        "           (@The_Date " & _
                        "           ,@User_Name " & _
                        "           ,@Period_ID " & _
                        "           ,@RID " & _
                        "           ,@A1 " & _
                        "           ,@A2 " & _
                        "           ,@A3 " & _
                        "           ,@A5 " & _
                        "           ,@A6 " & _
                        "           ,@A7 " & _
                        "           ,@A8 " & _
                        "           ,@A9 " & _
                        "           ,@A10 " & _
                        "           ,@A11 " & _
                        "           ,@A12 " & _
                        "           ,@EAG_Amount " & _
                        "           ,@Sch_All_Post16_Allocation" & _
                        "           ,@A15) "

                RunNonQuery(sql)



                'set the current allocation to history if applicable
                sql = "update Allocations set Status = 'History' where RID = '" & RID & "' and Allocation_ID like 'YPE%' and Period_ID = " & PeriodBox.SelectedValue
                RunNonQuery(sql)


                'now add it to the allocations table

                Dim y As Integer
                Dim Allocation_ID As String = ""
                Dim Allocation As Decimal = 0

                For y = 5 To 14

                    Select Case y
                        Case 5
                            Allocation_ID = "YPE01"
                            Allocation = A5 - Sch_All_Post16_Allocation
                        Case 6
                            Allocation_ID = "YPE02"
                            Allocation = A6
                        Case 7
                            Allocation_ID = "YPE03"
                            Allocation = A7
                        Case 8
                            Allocation_ID = "YPE04"
                            Allocation = A8
                        Case 9
                            Allocation_ID = "YPE05"
                            Allocation = A9
                        Case 10
                            Allocation_ID = "YPE06"
                            Allocation = A10
                        Case 11
                            Allocation_ID = "YPE07"
                            Allocation = A11
                        Case 12
                            Allocation_ID = "YPE08"
                            Allocation = A12
                        Case 13
                            'Dont do anything for the moment with EAG
                            'Allocation_ID = "YPE09"
                            'Allocation = EAG_Amount
                        Case 14
                            'which is actulay 15
                            Allocation_ID = "YPE09"
                            Allocation = A15
                            SetAdvancesAbatementsPayments(PeriodBox.SelectedValue.ToString, RID)
                    End Select

                    sql = " DECLARE @Period_ID as INT SET @Period_ID = " & PeriodBox.SelectedValue.ToString & _
                        " DECLARE @RID as nvarchar(11) SET @RID = '" & RID & "'" & _
                        " DECLARE @Allocation_ID as nchar(5) SET @Allocation_ID = '" & Allocation_ID & "'" & _
                        " DECLARE @The_Date as DateTime SET @The_Date = " & ApplyDate.ToOADate - 2 & _
                        " DECLARE @User_Name as nvarchar(50) SET @User_Name = '" & CurrentUser.Name & "'" & _
                        " DECLARE @Allocation AS MONEY SET @Allocation = " & Allocation & _
                        " DECLARE @Learner_Numbers AS INT SET @Learner_Numbers = " & A1 + A2 + A3 & _
                        " INSERT INTO [Allocations] " & _
                        "           ([Period_ID] " & _
                        "           ,[RID] " & _
                        "           ,[Allocation_ID] " & _
                        "           ,[TheDate] " & _
                        "           ,[User_Name] " & _
                        "           ,[Allocation] " & _
                        "           ,[Rate] " & _
                        "           ,[Provider_Factor] " & _
                        "           ,[Learner_Numbers] " & _
                        "           ,[Status] " & _
                        "           ,[Notes]) " & _
                        "     VALUES " & _
                        "           (@Period_ID " & _
                        "           ,@RID " & _
                        "           ,@Allocation_ID " & _
                        "           ,@The_Date " & _
                        "           ,@User_Name " & _
                        "           ,@Allocation " & _
                        "           ,0 " & _
                        "           ,0 " & _
                        "           ,@Learner_Numbers " & _
                        "           ,'Approved' " & _
                        "           ,'') "

                    RunNonQuery(sql)

                    UpdateActivePayments(PeriodBox.SelectedValue, RID, Allocation_ID)

                Next

            End If
        Next
        HideLoadingForm()

        MessageBox.Show("The Allocations have been applied", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        LoadNonCONData()

    End Sub

    Private Sub SetAdvancesAbatementsPayments(Period_ID As Integer, RID As String)

        Dim sql As String = ""
        Dim Year As Integer = 0

        'first delete all current payments

        sql = "delete from Payments where Period_ID = " & Period_ID & " and RID = '" & RID & "' and Allocation_ID = 'YPE09'"
        RunNonQuery(sql)

        Year = Mid(Period_ID.ToString, 1, 4)

        sql = "insert into payments select " & _
                "    Period_ID, RID, 'YPE09' as Allocation_ID, GetDate() as Payment_Date, " & _
                "    cast(case " & _
                "      when Field_Code = 110 then '1 September " & Year & "' " & _
                "      when Field_Code = 120 then '1 October " & Year & "' " & _
                "      when Field_Code = 130 then '1 November " & Year & "' " & _
                "      when Field_Code = 140 then '1 December " & Year & "' " & _
                "      when Field_Code = 150 then '1 January " & Year + 1 & "' " & _
                "      when Field_Code = 160 then '1 February " & Year + 1 & "' " & _
                "      when Field_Code = 170 then '1 March " & Year + 1 & "' " & _
                "      when Field_Code = 180 then '1 April " & Year + 1 & "' " & _
                "      when Field_Code = 190 then '1 May " & Year + 1 & "' " & _
                "      when Field_Code = 200 then '1 June " & Year + 1 & "' " & _
                "      when Field_Code = 210 then '1 July " & Year + 1 & "' " & _
                "      when Field_Code = 220 then '1 August " & Year + 1 & "' " & _
                "      else " & _
                "      ''  " & _
                "    end as DateTime) as Month, " & _
                "    Value as Month_Total " & _
                "from dbo.Acad_GAG_Data " & _
                "where Period_ID = " & Period_ID & " " & _
                "and Dataset_Code = 190 " & _
                "and Status = 'Approved' " & _
                "and RID = '" & RID & "'"

        RunNonQuery(sql)
    End Sub
End Class
